using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading;
using System.Globalization;
using System.Net;
namespace Test.Support {
	class PwmsWTC {
		static void MainX() {
			string sqlf=@"select sum(Pallets),sum(PalletSaving)from(
select Item,YearSold,WeeklyAvgItems,Items,Pallets,case when PalletSaving>0 then PalletSaving else 0 end PalletSaving,fifo from(
select Item,YearSold,WeeklyAvgItems,Items,Pallets,floor(Pallets-{0}*WeeklyAvgItems*Pallets/Items)PalletSaving,fifo from(
select ItemNo+ItemOption Item,count(*)YearSold,count(*)*7/365. WeeklyAvgItems
from tblWhseOrderView(nolock) where surname!='STOREDISPLAY' and OrderDate>getdate()-365
group by ItemNo+ItemOption)t1 join(select substring(skuCode,1,8)ItemFromTm,count(*)Pallets,sum(skuQuantity)Items,min(fifoDate)fifo
from pwmsTM(nolock)group by substring(skuCode,1,8)having sum(skuQuantity)>0
)t2 on Item=ItemFromTm)t3)t4";
			DAL pwms=new DAL("pon-wpsq21-db06\\in06","Pwms");
			for(int i=0;i<50;i++)
			{
				DAL dal=new DAL();
				DataTable tab=pwms.Query(sqlf,i);
				int all=(int)tab.Rows[0][0];
				int saved=(int)(decimal)tab.Rows[0][1];
				Console.WriteLine(i+"\t"+saved*100/all);
			}
		}
		static void MainX2() {
			Thread.CurrentThread.CurrentCulture=new CultureInfo(1053);
			//select ItemNo+ItemOption Item,OrderDate,count(*)cnt from tblWhseOrderView(nolock) where surname!='STOREDISPLAY'group by ItemNo+ItemOption,OrderDate
			DAL local=new DAL("(local)\\sql2012","PwmsTest");
			local.NonQry("truncate table SalesStat");
			//local.NonQry("drop table SalesStat");
			//local.NonQry("create table SalesStat(item varchar(8),dat date,period int,sold int,isaved decimal(10,2),psaved int)");
			DAL pwms=new DAL("pon-wpsq21-db06\\in06","Pwms");
			//DataTable items=local.Query("select distinct Item from PwmsOrders where OrderDate>getdate()-30 order by 1");
			DataTable items=pwms.Query("select ItemNo+ItemOption Item,count(*)cnt from tblWhseOrderView(nolock) where surname!='STOREDISPLAY' and OrderDate>getdate()-30 group by ItemNo+ItemOption order by 1");
			foreach(DataRow row in items.Rows)
			{
				string item=row["Item"].ToString();
				DataTable PwmsQty=pwms.Query("select isnull(sum(skuQuantity),0)qty,count(*)cnt from pwmsTM(nolock)where skuCode like '{0}%'",item);
				int TMs=(int)PwmsQty.Rows[0]["cnt"];
				if(TMs<20)
					continue;
				{
					Console.WriteLine(item);
					int ItemQty=(int)PwmsQty.Rows[0]["qty"];
					float itemsOnPallet=(float)ItemQty/TMs;
					DataTable dailySales=pwms.Query("select OrderDate,count(*)cnt from tblWhseOrderView(nolock) where ItemNo+ItemOption='{0}' and surname!='STOREDISPLAY' and OrderDate>getdate()-365 group by ItemNo+ItemOption,OrderDate order by OrderDate",item);
					//DataTable dailySales=local.Query("select * from PwmsOrders where Item='{0}' order by OrderDate",item);
					DateTime firstDay=(DateTime)dailySales.Rows[0]["OrderDate"];
					do
					{
						for(int periodDays=1;periodDays<=30;periodDays++)
						{
							long sold=0;
							string dayStart=firstDay.ToShortDateString();
							string dayEnd=firstDay.AddDays(periodDays).ToShortDateString();
							object soldObj=dailySales.Compute("sum(cnt)",string.Format("OrderDate>='{0}' and OrderDate<'{1}'",dayStart,dayEnd));
							if(soldObj is long)
								sold=(long)soldObj;
							float isaved=(float)((float)(ItemQty-sold)/ItemQty);
							int psaved=(int)Math.Floor(ItemQty/itemsOnPallet);
							local.NonQry("insert into SalesStat(item,dat,period,sold,isaved,psaved)values('{0}','{1}',{2},{3},{4},{5})",item,dayStart,periodDays,sold,isaved.ToString("0.00",new CultureInfo(1033)),psaved);
						}
						firstDay=firstDay.AddDays(1);
					} while(firstDay<DateTime.Now);
				}
			}
		}
	}
}
